package com.ubst.smartad.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.ubst.smartad.dao.TSAUserAccountDO;
import com.ubst.smartad.logger.Debug;

public class TSAUserAccountMngImpl {
	
	Connection con = null;
	private String serverName;
	private Debug debug;
	
	/** 
     * 생성자
     *   
    */ 
	public TSAUserAccountMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.serverName = serverName;
		this.debug = new Debug("TSAUserAccountMngImpl", log_level);
	}
	
	/**
	 * 사용자가 사용가능한 시스템의 계정정보(신청포함) 등록
	 * @param userAccountDO
	 * @throws SQLException
	 */
	public void insertUserAccount(TSAUserAccountDO userAccountDO) throws SQLException {
		PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        
        try {
            queryBuf = new StringBuffer();
			queryBuf.append("INSERT INTO TSA_USER_ACCOUNT " )
				.append(" ( ")
				.append("  USERID ,SYSTEMID ,ACCOUNTID ,SYSTEMNAME ,STARTDATE ")
				.append(" ,ENDDATE ,STARTTIME ,ENDTIME ,ACCUSAGE ,NSTATUS ")
				.append(" ,REQDATE ,APPDATE ")
				.append(" ) ")
				.append(" VALUES")
				.append(" ( ") 
				.append("  ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ,? ,? ")
				.append(" ) ");

			debug.debug("insertUserAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userAccountDO.userid);
			pstmt.setString(idx++, userAccountDO.systemid);
			pstmt.setString(idx++, userAccountDO.accountid);
			pstmt.setString(idx++, userAccountDO.systemname);
			pstmt.setString(idx++, userAccountDO.startdate);
			pstmt.setString(idx++, userAccountDO.enddate);
			pstmt.setString(idx++, userAccountDO.starttime);
			pstmt.setString(idx++, userAccountDO.endtime);
			pstmt.setString(idx++, userAccountDO.accusage);
			pstmt.setInt(idx++, userAccountDO.nstatus);
			pstmt.setString(idx++, userAccountDO.reqdate);
			pstmt.setString(idx++, userAccountDO.appdate);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;
        } catch (SQLException e){
			throw e;
        } finally {
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }
	}
	
	/**
	 * 사용자가 사용가능한 시스템의 계정정보(신청포함) 수정
	 * @param userAccountDO
	 * @throws SQLException
	 */
	public void updateUserAccount(TSAUserAccountDO userAccountDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {
            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_USER_ACCOUNT " )
				.append(" SET ")
				.append("  USERID=? ,SYSTEMID=? ,ACCOUNTID=? ,SYSTEMNAME=? ,STARTDATE=? ")
				.append(" ,ENDDATE=? ,STARTTIME=? ,ENDTIME=? ,ACCUSAGE=? ,NSTATUS=? ")
				.append(" ,REQDATE=? ,APPDATE=? ")
				.append(" WHERE USERID=? ");

			debug.debug("updateUserAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userAccountDO.userid);
			pstmt.setString(idx++, userAccountDO.systemid);
			pstmt.setString(idx++, userAccountDO.accountid);
			pstmt.setString(idx++, userAccountDO.systemname);
			pstmt.setString(idx++, userAccountDO.startdate);
			pstmt.setString(idx++, userAccountDO.enddate);
			pstmt.setString(idx++, userAccountDO.starttime);
			pstmt.setString(idx++, userAccountDO.endtime);
			pstmt.setString(idx++, userAccountDO.accusage);
			pstmt.setInt(idx++, userAccountDO.nstatus);
			pstmt.setString(idx++, userAccountDO.reqdate);
			pstmt.setString(idx++, userAccountDO.appdate);
			pstmt.setString(idx++, userAccountDO.userid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }
	
	/**
	 * 사용자가 사용가능한 시스템의 계정정보(신청포함) 목록 조회
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public TSAUserAccountDO selectUserAccount(String userid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSAUserAccountDO userAccountDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  USERID ,SYSTEMID ,ACCOUNTID ,SYSTEMNAME ,STARTDATE ")
				.append(" ,ENDDATE ,STARTTIME ,ENDTIME ,ACCUSAGE ,NSTATUS ")
				.append(" ,REQDATE ,APPDATE ")
				.append(" FROM TSA_USER_ACCOUNT ")
				.append(" WHERE USERID=? ");

			debug.debug("selectUserAccountList ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userid);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				userAccountDO = new TSAUserAccountDO();
				userAccountDO.userid = userid;
				userAccountDO.systemid = rs.getString("SYSTEMID");
				userAccountDO.accountid = rs.getString("ACCOUNTID");
				userAccountDO.systemname = rs.getString("SYSTEMNAME");
				userAccountDO.startdate = rs.getString("STARTDATE");
				userAccountDO.enddate = rs.getString("ENDDATE");
				userAccountDO.starttime = rs.getString("STARTTIME");
				userAccountDO.endtime = rs.getString("ENDTIME");
				userAccountDO.accusage = rs.getString("ACCUSAGE");
				userAccountDO.nstatus = rs.getInt("NSTATUS");
				userAccountDO.reqdate = rs.getString("REQDATE");
				userAccountDO.appdate = rs.getString("APPDATE");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return userAccountDO;
	}
	
	/**
	 * 사용자가 사용가능한 시스템의 계정정보(신청포함) 삭제
	 * @param userid
	 * @throws SQLException
	 */
	public void deleteUserAccount(String userid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_USER_ACCOUNT " )
				.append(" WHERE USERID=? ");

			debug.debug("deleteUserAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }
}
